package com.asm.DBloader;

import java.text.DecimalFormat;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class ASM_FOrdDetDS {
	private SQLiteDatabase dB;
	private ASM_DB dbHelper;
	Context context;
	
	
	public ASM_FOrdDetDS (Context context) {
		this.context = context;
		dbHelper = new ASM_DB(context);
		
	}
	
	public void open() throws SQLException
	{
		dB = dbHelper.getWritableDatabase();
	}
	
	//*********Add your method
	
	@SuppressWarnings("static-access")
	public int Create(ASM_FOrdDet det) {
		
		int count =0;
		
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		
		
		ContentValues values = new ContentValues();
		values.put(ASM_DB.asmDET_RefNo, det.getAsmDET_RefNo());
		values.put(ASM_DB.asmDET_TxnDate, det.getAsmDET_TxnDate());
		values.put(ASM_DB.asmDET_TxnType,det.getAsmDET_TxnType());
		values.put(ASM_DB.asmDET_SeqNo, det.getAsmDET_SeqNo());
		values.put(ASM_DB.asmDET_Itemcode, det.getAsmDET_Itemcode());
		values.put(ASM_DB.asmDET_Txnseqno,det.getAsmDET_Txnseqno());
		values.put(ASM_DB.asmDET_SellPrice, det.getAsmDET_SellPrice());
		values.put(ASM_DB.asmDET_BSellPrice, det.getAsmDET_BSellPrice());
		values.put(ASM_DB.asmDET_TSellPrice,det.getAsmDET_TSellPrice());
		values.put(ASM_DB.asmDET_BTSellPrice, det.getAsmDET_BTSellPrice());
		values.put(ASM_DB.asmDET_CostPrice, det.getAsmDET_CostPrice());
		values.put(ASM_DB.asmDET_CaseQty,det.getAsmDET_CaseQty());
		values.put(ASM_DB.asmDET_PiceQty, det.getAsmDET_PiceQty());
		values.put(ASM_DB.asmDET_Type, det.getAsmDET_Type());
		values.put(ASM_DB.asmDET_Qty,det.getAsmDET_Qty());
		values.put(ASM_DB.asmDET_BalQty, det.getAsmDET_BalQty());
		values.put(ASM_DB.asmDET_DisPer, det.getAsmDET_DisPer());
		values.put(ASM_DB.asmDET_DisAmt,det.getAsmDET_DisAmt());
		values.put(ASM_DB.asmDET_BDisAmt, det.getAsmDET_BDisAmt());
		values.put(ASM_DB.asmDET_TaxAmt, det.getAsmDET_TaxAmt());
		values.put(ASM_DB.asmDET_BTaxAmt,det.getAsmDET_BTaxAmt());
		values.put(ASM_DB.asmDET_Amt, det.getAsmDET_Amt());
		values.put(ASM_DB.asmDET_BAmt, det.getAsmDET_BAmt());
		values.put(ASM_DB.asmDET_ReaCode,det.getAsmDET_ReaCode());
		values.put(ASM_DB.asmDET_RecordId, det.getAsmDET_RecordId());
		values.put(ASM_DB.asmDET_DisPerAmt, det.getAsmDET_DisPerAmt());
		values.put(ASM_DB.asmDET_DisValAmt,det.getAsmDET_DisValAmt());
		values.put(ASM_DB.asmDET_FrIss, det.getAsmDET_FrIss());

		count = (int) dB.insert(ASM_DB.ASM_FORDDET, null, values);

		dB.close();
		return count;
		
	}
	
	public ArrayList<String> getFreeissuerebdetails1(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) 
		{
			open();
		} else if (!dB.isOpen()) 
		{
			open();
		}
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select a.RefNo,a.TxnDate,c.DebName,d.ItemName,b.CaseQty,b.PiceQty from FOrdHed as a inner join FOrddet as b on a.RefNo = b.RefNo inner join fDebtor as c on a.DebCode = c.DebCode inner join fItems as d on b.Itemcode = d.ItemCode where a.RepCode = '"+Repcode+"' and b.Type = 'FD' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		System.out.println(cursor.getCount());
		
		while(cursor.moveToNext())
		{
		
			String RefNo = cursor.getString(0);
			Details.add(RefNo);
		
		}
		
	
		return Details;
	}
	public ArrayList<String> getFreeissuerebdetails2(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) 
		{
			open();
		} else if (!dB.isOpen()) 
		{
			open();
		}
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select a.RefNo,a.TxnDate,c.DebName,d.ItemName,b.CaseQty,b.PiceQty from FOrdHed as a inner join FOrddet as b on a.RefNo = b.RefNo inner join fDebtor as c on a.DebCode = c.DebCode inner join fItems as d on b.Itemcode = d.ItemCode where a.RepCode = '"+Repcode+"' and b.Type = 'FD' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
		
			String TxnDate = cursor.getString(1);
			Details.add(TxnDate);
		
		}
		
	
		return Details;
	}
	public ArrayList<String> getFreeissuerebdetails3(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) 
		{
			open();
		} else if (!dB.isOpen()) 
		{
			open();
		}
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select a.RefNo,a.TxnDate,c.DebName,d.ItemName,b.CaseQty,b.PiceQty from FOrdHed as a inner join FOrddet as b on a.RefNo = b.RefNo inner join fDebtor as c on a.DebCode = c.DebCode inner join fItems as d on b.Itemcode = d.ItemCode where a.RepCode = '"+Repcode+"' and b.Type = 'FD' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
		
			String DebName = cursor.getString(2);
			Details.add(DebName);
		
		}
		
	
		return Details;
	}
	public ArrayList<String> getFreeissuerebdetails4(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) 
		{
			open();
		} else if (!dB.isOpen()) 
		{
			open();
		}
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select a.RefNo,a.TxnDate,c.DebName,d.ItemName,b.CaseQty,b.PiceQty from FOrdHed as a inner join FOrddet as b on a.RefNo = b.RefNo inner join fDebtor as c on a.DebCode = c.DebCode inner join fItems as d on b.Itemcode = d.ItemCode where a.RepCode = '"+Repcode+"' and b.Type = 'FD' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
		
			String ItemName = cursor.getString(3);
			Details.add(ItemName);
		
		}
		
	
		return Details;
	}
	public ArrayList<String> getFreeissuerebdetails5(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) 
		{
			open();
		} else if (!dB.isOpen()) 
		{
			open();
		}
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select a.RefNo,a.TxnDate,c.DebName,d.ItemName,b.CaseQty,b.PiceQty from FOrdHed as a inner join FOrddet as b on a.RefNo = b.RefNo inner join fDebtor as c on a.DebCode = c.DebCode inner join fItems as d on b.Itemcode = d.ItemCode where a.RepCode = '"+Repcode+"' and b.Type = 'FD' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
		
			String CaseQty = cursor.getString(4);
			Details.add(CaseQty);
		
		}
		
	
		return Details;
	}
	public ArrayList<String> getFreeissuerebdetails6(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) 
		{
			open();
		} else if (!dB.isOpen()) 
		{
			open();
		}
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select a.RefNo,a.TxnDate,c.DebName,d.ItemName,b.CaseQty,b.PiceQty from FOrdHed as a inner join FOrddet as b on a.RefNo = b.RefNo inner join fDebtor as c on a.DebCode = c.DebCode inner join fItems as d on b.Itemcode = d.ItemCode where a.RepCode = '"+Repcode+"' and b.Type = 'FD' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
		
			String PiceQty = cursor.getString(5);
			Details.add(PiceQty);
		
		}
		
	
		return Details;
	}
	
	public ArrayList<String> getFreeisscount(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) 
		{
			open();
		} else if (!dB.isOpen()) 
		{
			open();
		}
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select sum(b.CaseQty),sum(b.PiceQty) from FOrdHed as a inner join FOrddet as b on a.RefNo = b.RefNo inner join fDebtor as c on a.DebCode = c.DebCode inner join fItems as d on b.Itemcode = d.ItemCode where a.RepCode = '"+Repcode+"' and b.Type = 'FD' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
		
			String CaseQtycount = cursor.getString(0);
			String PiceQtycount = cursor.getString(1);
			Details.add(CaseQtycount);
			Details.add(PiceQtycount);
		
		}
		
	
		return Details;
	}
	
	public String getArea(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
	
		String AreaCode = null;
		String selectQuery = "select RegName from Fregion where RegCode in(select RegCode from fArea where AreaCode in(select a.AreaCode from FOrdHed as a inner join FOrddet as b on a.RefNo = b.RefNo inner join fDebtor as c on a.DebCode = c.DebCode inner join fItems as d on b.Itemcode = d.ItemCode where a.RepCode = '"+Repcode+"' and b.Type = 'FD' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'))";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
		
			AreaCode = cursor.getString(0);
		
		}
		
		return AreaCode;
	}
	
	public ArrayList<String> gettotamt(String Repname) 
	{
		if (dB == null) 
		{
			open();
		} else if (!dB.isOpen()) 
		{
			open();
		}
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select sum(TotalAmt) from FOrdHed where RepCode = '"+Repname+"'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
		
			String totalamt = cursor.getString(0);
			Details.add(totalamt);
		
		}
		
	
		return Details;
	}
	
	public ArrayList<String> getLocationRept(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
	
		ArrayList<String> Details = new ArrayList<String>();
		//String selectQuery = "select distinct(a.RefNo),a.TxnDate,c.AreaName,a.TxnDate,b.address,d.DebName,d.DebName,COUNT(a.Itemcode),b.TotalAmt,e.RepName from FOrddet as a, FOrdhed as b, fArea as c, fDebtor as d, fSalRep as e where a.RefNo=b.RefNo and b.AreaCode = c.AreaCode and b.DebCode = d.DebCode and e.RepCode =b.RepCode and b.RepCode = '"+Repcode+"' and strftime('%Y-%m-%d',b.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"'  group by b.refno";

		String selectQuery = "select a.TxnDate,a.RefNo,b.AreaName,a.startTimeSO,a.address,c.DebName,a.DiffDate,a.Skutype,a.TotalAmt from FordHed as a inner join FArea as b on a.AreaCode = b.AreaCode inner join Fdebtor as c on a.DebCode = c.DebCode where a.RepCode ='"+Repcode+"' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+enddate+"' and TxnType = '21' group by a.RefNo";
	
		
		
		
		
		
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		System.out.println(cursor.getCount());
		while(cursor.moveToNext())
		{
		
			String LIst = 	 cursor.getString(0)+":-:"
							+cursor.getString(1)+":-:"
							+cursor.getString(2)+":-:"
							+cursor.getString(3)+":-:"
							+cursor.getString(4)+":-:"
							+cursor.getString(5)+":-:"
							+cursor.getString(6)+":-:"
							+cursor.getString(7)+":-:"
							+cursor.getString(8);
			
					Details.add(LIst);
		
		}
		cursor.close();
		return Details;
	}
	
	public ArrayList<String> getFirstsecondlastsummery(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		DecimalFormat df = new DecimalFormat("0.00");
		df.setMaximumFractionDigits(2);
	
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select d.AreaName,c.RepName,a.address,a.startTimeSO,a.TotalAmt from FordHed as a inner join farea as b on a.AreaCode = b.AreaCode inner join Fsalrep as c on a.RepCode = c.RepCode inner join Farea as d on a.AreaCode = d.AreaCode where a.RepCode = '"+Repcode+"' and a.TxnType = '21' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+startdate+"' order by a._ID ASC limit 2";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		System.out.println(cursor.getCount());
		while(cursor.moveToNext())
		{
		
			String LIst =  cursor.getString(0)+":-:"
							+cursor.getString(1)+":-:"
							+cursor.getString(2)+":-:"
							+cursor.getString(3)+":-:"
							+df.format(cursor.getDouble(4));
			
					Details.add(LIst);
		
		}
		cursor.close();
		return Details;
	}
	
	public ArrayList<String> getFirstsecondlastsummerylast(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		DecimalFormat df = new DecimalFormat("0.00");
		df.setMaximumFractionDigits(2);
	
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select d.AreaName,c.RepName,a.address,a.startTimeSO,a.TotalAmt from FordHed as a inner join farea as b on a.AreaCode = b.AreaCode inner join Fsalrep as c on a.RepCode = c.RepCode inner join Farea as d on a.AreaCode = d.AreaCode where a.RepCode = '"+Repcode+"' and a.TxnType = '21' and strftime('%Y-%m-%d',a.TxnDate) BETWEEN '"+startdate+"' and '"+startdate+"'  order by a._ID DESC limit 1";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		System.out.println(cursor.getCount());
		while(cursor.moveToNext())
		{
			
		
			String LIst =  cursor.getString(0)+":-:"
							+cursor.getString(1)+":-:"
							+cursor.getString(2)+":-:"
							+cursor.getString(3)+":-:"
							+df.format(cursor.getDouble(4));
			
					Details.add(LIst);
		
		}
		cursor.close();
		return Details;
	}
	
	
	public ArrayList<String> getFirstsecondlastsummeryTotVal(String startdate,String enddate,String Repcode) 
	{
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		DecimalFormat df = new DecimalFormat("0.00");
		df.setMaximumFractionDigits(2);
		//stringLitersOfPetrol = df.format(litersOfPetrol);
	
		ArrayList<String> Details = new ArrayList<String>();
		String selectQuery = "select sum(TotalAmt),count(RefNo) from FordHed where Repcode = '"+Repcode+"' and TxnDate = '"+startdate+"' and TxnType = '21'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		System.out.println(cursor.getCount());
		while(cursor.moveToNext())
		{
			Double avg = cursor.getDouble(0)/cursor.getDouble(1);
			
			String LIst =  df.format(cursor.getDouble(0))+":-:"
							+df.format(cursor.getDouble(1))+":-:"
							+df.format(avg);
			
					Details.add(LIst);
		
		}
		cursor.close();
		return Details;
	}
}
